package com.lm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.lm.dao.ProductDAO;
import com.lm.dto.DiscountDTO;
import com.lm.dto.ProductDTO;
import com.lm.dto.SearchCondition;
import com.lm.model.Product;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.util.DBUtil;
import com.util.MathUtil;

public class ProductDAOImpl implements ProductDAO {
	private int RECOMMEND_PRODUCT_NUM = 15;

	private int HOT_PRODUCT_NUM = 4;

	private int SPECAIL_PRODUCT_NUM = 3;

	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List getSpecialProductList() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where isConfirm=1 ");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}

			if (resList.size() > SPECAIL_PRODUCT_NUM)
				resList = resList.subList(0, SPECAIL_PRODUCT_NUM);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getSpeciaproductroductList(String typeID, String brandID)
			throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where typeID= ").append(Integer.parseInt(typeID));
		sb.append(" and brandID=").append(Integer.parseInt(brandID));
		sb.append(" and isConfirm=1 ");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}

			if (resList.size() > SPECAIL_PRODUCT_NUM)
				resList = resList.subList(0, SPECAIL_PRODUCT_NUM);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	public List getSpeciaproductroductList(String typeID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where typeID= ").append(Integer.parseInt(typeID));
		sb.append(" and isConfirm=1 ");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}

			if (resList.size() > SPECAIL_PRODUCT_NUM)
				resList = resList.subList(0, SPECAIL_PRODUCT_NUM);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getRecommendProduct() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where isSelect=1 ");
		sb.append(" and isConfirm=1 ");
		sb.append(" order by brandID");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
			if (resList.size() > RECOMMEND_PRODUCT_NUM)
				resList = resList.subList(0, RECOMMEND_PRODUCT_NUM);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getRecommendProduct(String typeID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where isSelect=1 ");
		sb.append(" and isConfirm=1 ");
		sb.append(" and typeID= ").append(Integer.parseInt(typeID));

		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
			if (resList.size() > RECOMMEND_PRODUCT_NUM)
				resList = resList.subList(0, RECOMMEND_PRODUCT_NUM);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	public List getRecommendProduct(String typeID, String brandID)
			throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where isSelect=1 ");
		sb.append(" and isConfirm=1 ");
		sb.append(" and typeID= ").append(Integer.parseInt(typeID));
		sb.append(" and brandID=").append(Integer.parseInt(brandID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
			if (resList.size() > RECOMMEND_PRODUCT_NUM)
				resList = resList.subList(0, RECOMMEND_PRODUCT_NUM);

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getHotProduct() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where isConfirm=1 ");
		sb.append(" order by hit desc");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
			if (resList.size() > HOT_PRODUCT_NUM)
				resList = resList.subList(0, HOT_PRODUCT_NUM);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getHotProduct(String typeID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where typeID= ").append(Integer.parseInt(typeID));
		sb.append(" and isConfirm=1 ");
		sb.append(" order by hit desc");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
			if (resList.size() > HOT_PRODUCT_NUM - 1)
				resList = resList.subList(0, HOT_PRODUCT_NUM - 1);

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getHotProduct(String typeID, String brandID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where typeID= ").append(Integer.parseInt(typeID));
		sb.append(" and brandID=").append(Integer.parseInt(brandID));
		sb.append(" and isConfirm=1 ");
		sb.append(" order by hit desc");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
			if (resList.size() > HOT_PRODUCT_NUM - 1)
				resList = resList.subList(0, HOT_PRODUCT_NUM - 1);

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public DiscountDTO getTotalDiscountInfo() throws Throwable {
		DiscountDTO discountDTO = new DiscountDTO();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb
				.append(" select sum(mount) totalmount,sum(totaproductrice) totaproductrice,sum(totalDisBid) totaldisbid from lm_discount  ");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				discountDTO.setTotalMount(String.valueOf(MathUtil
						.operationDouble(rs.getInt("totalmount"))));
				discountDTO.setTotalDisBid(String.valueOf(MathUtil
						.operationDouble(rs.getDouble("totaldisbid"))));
				discountDTO.setTotalPrice(String.valueOf(MathUtil
						.operationDouble(rs.getDouble("totaproductrice"))));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return discountDTO;
	}

	public List getProductListByUserID(int userID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where specUserID= ").append(userID);
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	public List getAllConfirmProductList() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where isConfirm=1 ");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getAlproductroductList() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		// sb.append(" where specUserID= ").append(userID);
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));
				product.setIsConfirm(rs.getInt("isConfirm"));
				resList.add(product);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getBrandIDAndTypeIDGroupBy() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select typeid,brandid from lm_product  ");
		sb.append(" group by typeID,brandid ");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();

				product.setBrandId(rs.getInt("brandID"));
				product.setTypeId(rs.getInt("typeID"));

				resList.add(product);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public Product getProductByUserIDAndProductID(int userID, String productID)
			throws Throwable {
		Product product = new Product();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where specUserID= ").append(userID);
		sb.append(" and id =").append(Integer.parseInt(productID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return product;
	}

	public Product getProductByProductID(String productID) throws Throwable {
		Product product = new Product();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product ");
		sb.append(" where id =").append(Integer.parseInt(productID));

		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return product;
	}

	public List getProductByBrandID(String brandID) throws Throwable {
		List resList = new ArrayList();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where brandID =").append(Integer.parseInt(brandID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public String getProductIDsBySupporterID(String supporterID)
			throws Throwable {
		StringBuilder productIDs = new StringBuilder();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select id from lm_product  ");
		sb.append(" where specUserID =").append(Integer.parseInt(supporterID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				productIDs.append(rs.getInt("id")).append(",");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return productIDs.toString();
	}

	public List getResultProduct(SearchCondition search) throws Throwable {
		List resList = new ArrayList();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb
				.append(" select product.*,lsp.* from lm_product product,lm_subproduct lsp ");
		sb.append(" where product.id =lsp.productID");

		if (search.getKeyWord() != null && !search.getKeyWord().equals("")) {
			sb.append(" and (product.productNo like '%").append(
					search.getKeyWord()).append("%'");
			// sb.append(" or product.productName like
			// '%").append(form.getKeyWord()).append("%'");
			sb.append(" or lsp.attribute like '%").append(search.getKeyWord())
					.append("%'");
			sb.append(" or lsp.proDetailinfo like '%").append(
					search.getKeyWord()).append("%'");
			sb.append(" or lsp.techParam like '%").append(search.getKeyWord())
					.append("%')");
		}
		if (search.getColor() != null && !search.getColor().equals("")) {
			sb.append(" and (lsp.attribute like '%").append(search.getColor())
					.append("%'");
			sb.append(" or lsp.proDetailinfo like '%")
					.append(search.getColor()).append("%'");
			sb.append(" or lsp.techParam like '%").append(search.getColor())
					.append("%')");
		}
		if (search.getMaxBid() != null && search.getMinBid() != null) {
			sb.append(" and (product.lowPrice >=").append(search.getMinBid());
			sb.append(" and product.lowPrice <=").append(search.getMaxBid())
					.append(")");
		}
		if (search.getHavePic() != null) {
			sb.append(" and product.picName <> ''");
		}
		if (search.getBrandID() != null
				&& Integer.parseInt(search.getBrandID()) != 0) {
			sb.append(" and product.brandID =").append(
					Integer.parseInt(search.getBrandID()));
		}
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				ProductDTO productDTO = new ProductDTO();

				productDTO.setPrice(String.valueOf(rs
						.getDouble("product.price")));
				productDTO.setLowPrice(String.valueOf(rs
						.getDouble("product.lowPrice")));
				productDTO.setBrandId(String.valueOf(rs
						.getInt("product.brandID")));
				productDTO
						.setProductID(String.valueOf(rs.getInt("product.id")));
				productDTO.setIsSelect(String.valueOf(rs
						.getInt("product.isSelect")));
				// lmDTO.setProductName(rs.getString("product.productName"));
				productDTO.setProductNo(rs.getString("product.productNo"));
				productDTO.setSpecUserId(String.valueOf(rs
						.getInt("product.specUserID")));
				productDTO.setTypeId(String
						.valueOf(rs.getInt("product.typeID")));
				productDTO.setPicName(rs.getString("product.picName"));
				productDTO.setPicUrl(rs.getString("product.picURL"));
				// lmDTO.setHit(String.valueOf(rs.getInt("product.hit")));
				productDTO.setAttribute(rs.getString("lsp.attribute"));
				productDTO.setTechParam(rs.getString("lsp.techParam"));
				productDTO.setProDetailinfo(rs.getString("lsp.proDetailinfo"));
				resList.add(productDTO);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getProductByBrandIDAndTypeID(int typeID, int brandID)
			throws Throwable {
		List resList = new ArrayList();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_product  ");
		sb.append(" where brandID =").append(brandID);
		sb.append(" and typeID=").append(typeID);
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setPrice(rs.getDouble("price"));
				product.setLowPrice(rs.getDouble("lowPrice"));
				product.setBrandId(rs.getInt("brandID"));
				product.setId(rs.getInt("id"));
				product.setIsSelect(rs.getInt("isSelect"));
				// product.setProductName(rs.getString("productName"));
				product.setProductNo(rs.getString("productNo"));
				product.setSpecUserId(rs.getInt("specUserID"));
				product.setTypeId(rs.getInt("typeID"));
				product.setPicName(rs.getString("picName"));
				product.setPicUrl(rs.getString("picURL"));
				product.setHit(rs.getInt("hit"));

				resList.add(product);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public int getProductIDByUserID(int userID) throws Throwable {
		int id = 0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select id from lm_product  ");
		sb.append(" where specUserID= ").append(userID);
		sb.append(" order by id desc limit 0,1");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				id = rs.getInt("id");

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return id;

	}

	public void addProductByUserID(Product product, int userID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" insert into lm_product(productNo,price,");
		sb.append(" lowPrice,typeID,brandID,specUserID,picURL,picName)");
		sb.append(" values('").append(product.getProductNo()).append("',");
		// sb.append("'").append(form.getProductName()).append("',");
		sb.append(product.getPrice()).append(",");
		sb.append(product.getLowPrice()).append(",");
		sb.append(product.getTypeId()).append(",");
		sb.append(product.getBrandId()).append(",");
		sb.append(userID).append(",");
		sb.append("'").append(product.getPicUrl()).append("',");
		sb.append("'").append(product.getPicName()).append("')");
		// sb.append("'").append(" ").append("')");
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editProductByUserIDAndProductID(Product product, int userID,
			String productID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();

		sb.append("update lm_product set productNo='").append(
				product.getProductNo());
		// sb.append("',productName='").append(form.getProductName());
		sb.append("',price=").append(product.getPrice());
		sb.append(",lowPrice=").append(product.getLowPrice());
		sb.append(",typeID=").append(product.getTypeId());
		sb.append(",brandID=").append(product.getBrandId());
		if (product.getPicName() != null) {
			sb.append(",picURL='").append(product.getPicUrl());
		}
		sb.append("',picName='").append(product.getPicName()).append("'");
		sb.append(" where specUserID=").append(userID);
		sb.append(" and  id=").append(Integer.parseInt(productID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editProductByProductID(Product product, String productID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("update lm_product set productNo='").append(
					product.getProductNo());
			// sb.append("',productName='").append(form.getProductName());
			sb.append("',price=").append(product.getPrice());
			sb.append(",lowPrice=").append(product.getLowPrice());
			if (product.getPicName() != null) {
				sb.append(",picURL='").append(product.getPicUrl());
			}
			sb.append("',isSelect=").append(product.getIsSelect());
			sb.append(",picName='").append(product.getPicName()).append("'");
			sb.append(",isConfirm=1");
			sb.append(" where id=").append(Integer.parseInt(productID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void updateHitOfProduct(int hit, int productID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("update lm_product set hit=").append(hit);
			sb.append(" where id=").append(productID);

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteProductByProductID(int productID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from lm_product ");
			sb.append(" where id=").append(productID);

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteProductByTypeID(int typeID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from lm_product ");
			sb.append(" where typeID=").append(typeID);

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteProductByUserID(int userID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_product ");
		sb.append(" where specUserID=").append(userID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}
}
